package com.han.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import com.han.bom.Professor;
import com.han.bom.Student;
import com.han.bom.User;

public class UserDAO {
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(UserDAO.class);
	
	public void connect() throws ClassNotFoundException, SQLException{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/beta_test_engine", "root", "");
		}catch(ClassNotFoundException e){
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}
	}
	
	public void disconnect() throws SQLException{
		if(this.resultSet!=null){
			try{
				this.resultSet.close();
			}catch(SQLException e){
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.preparedStatement!=null){
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.connection!=null){
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
	}
	
	public ArrayList<User> retrieveAllUsers() throws ClassNotFoundException, SQLException{
		ArrayList<User> users = new ArrayList<User>();
		Professor professor = null;
		Student student = null;
		Boolean type = null;
		
		try{
			this.connect();
			String select = "SELECT * FROM tbl_user";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean(6);
				
				if(type){
					professor = new Professor(this.resultSet.getInt(1));
					professor.setFirstName(this.resultSet.getString(2));
					professor.setLastName(this.resultSet.getString(3));
					professor.setUsername(this.resultSet.getString(4));
					professor.setPassword(this.resultSet.getString(5));
					
					users.add(professor);
				}else{
					student = new Student(this.resultSet.getInt(1));
					student.setFirstName(this.resultSet.getString(2));
					student.setLastName(this.resultSet.getString(3));
					student.setUsername(this.resultSet.getString(4));
					student.setPassword(this.resultSet.getString(5));
					
					users.add(student);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return users;
	}
	
	public User retrieveUserById(String userID) throws ClassNotFoundException, SQLException{
		User user = null;
		String id = userID.substring(1);
		int id2 = Integer.parseInt(id);
		Boolean type = null;
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_user WHERE user_id = ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id2);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean(6);
				
				if(type){
					user = new Professor(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				}else{
					user = new Student(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return user;
	}
	
	public User retrieveUserByUsername(String username) throws ClassNotFoundException, SQLException{
		User user = null;
		Boolean type = null;
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_user WHERE user_username = ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setString(1, username);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean(6);
				
				if(type){
					user = new Professor(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				}else{
					user = new Student(this.resultSet.getInt(1));
					user.setFirstName(this.resultSet.getString(2));
					user.setLastName(this.resultSet.getString(3));
					user.setUsername(this.resultSet.getString(4));
					user.setPassword(this.resultSet.getString(5));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return user;
	}
	
	public ArrayList<Student> retrieveStudentsByProfessor(String profID) throws ClassNotFoundException, SQLException{
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int id = Integer.parseInt(profID.substring(1));
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_user AS a INNER JOIN tbl_professor_student AS b ON a.user_id = b.student_id WHERE b.professor_id = ?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));
				
				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return students;
	}
	
	public ArrayList<Student> retrieveStudentsTakingExam(String examid) throws ClassNotFoundException, SQLException{
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int eid = 0;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_user AS a INNER JOIN tbl_student_exam AS b ON a.user_id = b.student_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));
				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return students;
	}
	
	public ArrayList<Student> retrieveStudentsByExamTaken(String examID) throws ClassNotFoundException, SQLException{
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int eid = 0;
		
		try{
			eid = Integer.parseInt(examID.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_user AS a INNER JOIN tbl_exam_response AS b ON a.user_id=b.student_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));
				
				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return students;
	}
	
	public ArrayList<Student> retrieveStudentsByKeyword(String key, String profID) throws ClassNotFoundException, SQLException{
		ArrayList<Student> students = new ArrayList<Student>();
		Student student = null;
		int id = Integer.parseInt(profID.substring(1));
		int stid = 0;
		String intkey = "%"+key+"%";
		
		try{
			stid = Integer.parseInt(key.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			stid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT DISTINCT * FROM tbl_user AS a INNER JOIN tbl_professor_student AS b ON a.user_id = b.student_id WHERE b.professor_id = ? AND (a.user_id = ? OR a.user_first_name LIKE ? OR a.user_last_name LIKE ? OR a.user_username LIKE ?)";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, stid);
			this.preparedStatement.setString(3, intkey);
			this.preparedStatement.setString(4, intkey);
			this.preparedStatement.setString(5, intkey);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				student = new Student(this.resultSet.getInt(1));
				student.setFirstName(this.resultSet.getString(2));
				student.setLastName(this.resultSet.getString(3));
				student.setUsername(this.resultSet.getString(4));
				student.setPassword(this.resultSet.getString(5));
				
				students.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return students;
	}
	
	public int retrieveMaxUserID() throws ClassNotFoundException, SQLException{
		int id = 0;
		
		try {
			this.connect();
			String select = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'beta_test_engine' AND TABLE_NAME = 'tbl_user'";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				id = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return id;
	}
	
	public boolean addStudentToProfessor(String studentId, String profId) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String insert = "INSERT IGNORE INTO tbl_professor_student(professor_id,student_id) VALUES((SELECT user_id FROM tbl_user WHERE user_id=?),(SELECT user_id FROM tbl_user WHERE user_id=?))";
		int profid = 0;
		int studentid = 0;
		
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			try{
				profid = Integer.parseInt(profId.substring(1));
			}catch(NumberFormatException e){
				profid = 0;
			}
			
			try{
				studentid = Integer.parseInt(studentId.substring(1));
			}catch(NumberFormatException e){
				studentid = 0;
			}
			
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			this.preparedStatement.setInt(1, profid);
			this.preparedStatement.setInt(2, studentid);
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			
			success=(row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean insertUser(User user) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String insert = "INSERT INTO tbl_user(user_first_name,user_last_name,user_username,user_password,user_status,user_active) VALUES(?,?,?,?,?,?)";
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, user.getFirstName());
			this.preparedStatement.setString(2, user.getLastName());
			this.preparedStatement.setString(3, user.getUsername());
			this.preparedStatement.setString(4, user.getPassword());
			
			if(user instanceof Professor)
			{
				this.preparedStatement.setBoolean(5, true);
			}else{
				this.preparedStatement.setBoolean(5, false);
			}
			
			this.preparedStatement.setBoolean(6, true);
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success=(row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateUser(User user) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String insert = "UPDATE tbl_user SET user_first_name=?,user_last_name=?,user_username=?,user_password=?,user_status=?,user_active=? WHERE user_id=?";
		int userid = 0;
		
		try{
			userid = Integer.parseInt(user.getUserID().substring(1));
		}catch(NumberFormatException e){
			userid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, user.getFirstName());
			this.preparedStatement.setString(2, user.getLastName());
			this.preparedStatement.setString(3, user.getUsername());
			this.preparedStatement.setString(4, user.getPassword());
			
			if(user instanceof Professor)
			{
				this.preparedStatement.setBoolean(5, true);
			}else{
				this.preparedStatement.setBoolean(5, false);
			}
			
			this.preparedStatement.setBoolean(6, true);
			this.preparedStatement.setInt(7, userid);
			
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success=(row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean deleteUser(String userId) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String insert = "DELETE FROM tbl_user WHERE user_id=?";
		int id = 0;
		
		try{
			id = Integer.parseInt(userId.substring(1));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, id);
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success=(row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public PreparedStatement getPreparedStatement() {
		return preparedStatement;
	}

	public void setPreparedStatement(PreparedStatement preparedStatement) {
		this.preparedStatement = preparedStatement;
	}

	public ResultSet getResultSet() {
		return resultSet;
	}

	public void setResultSet(ResultSet resultSet) {
		this.resultSet = resultSet;
	}
}
